# -----------------------------------------------------------------------------
# Re-create table ensembl_mart_hsapiens_genes_go which contains the mapping of
# of GO terms to ensembl gene ids
# -----------------------------------------------------------------------------

library(biomaRt)
library(sqldf)
library(RODBC)

ensembl<- useMart("ENSEMBL_MART_ENSEMBL", host="www.ensembl.org")
mart<- useDataset("hsapiens_gene_ensembl", ensembl)
go2gene<- getBM(attributes= c( 
    "ensembl_gene_id", 
    "external_gene_id", 
    "go_id", 
    "name_1006", 
    "definition_1006",
    "go_linkage_type",
     "namespace_1003"),
     mart= mart
)

names(go2gene)<- c( 
    "ensembl_gene_id", 
    "external_gene_id", 
    "go_id",  
    "go_name", 
    "go_definition", 
    "go_evidence_code", 
    "go_domain"
)

go2gene_unique<- sqldf("select distinct * from go2gene where go_id != ''")
go2gene_unique[1:5,]

con<- odbcConnect('pgsblab')
sqlQuery(con, 'set search_path to extdata')
sqlQuery(con, 'drop table if exists extdata.ensembl_mart_hsapiens_genes_go')
sqlQuery(con, 'create table extdata.ensembl_mart_hsapiens_genes_go ( 
    "ensembl_gene_id" text, 
    "external_gene_id" text, 
    "go_id" text,  
    "go_name" text, 
    "go_definition" text, 
    "go_evidence_code" text, 
    "go_domain" text
    )'
)
sqlSave(con, go2gene_unique, tablename= 'ensembl_mart_hsapiens_genes_go', 
    rownames= FALSE, append= TRUE)
sqlQuery(con, paste("comment on table ensembl_mart_hsapiens_genes_go is 'Mapping of ensembl gene ids (H. sapiens) to GO terms. Produced by biomart with pg_upload_ensembl_mart_hsapiens_gene_go.R on", Sys.Date(), "'" ))
odbcClose(con)


